package controllers;

import java.sql.*;

import com.mysql.jdbc.PreparedStatement;

public class DAO {
	
	private Connection connexio; 
	private Statement sentencia;
	
	public DAO() throws Exception { 
		String user = "mysql";
		String password = "prac"; 
		Class.forName("com.mysql.jdbc.Driver").newInstance(); 
		connexio = DriverManager.getConnection("jdbc:mysql://localhost/ts1?user="+user+"&password="+password); 
		sentencia = connexio.createStatement();
	}

	// Executa consultes
	public ResultSet executarSQL(String query) throws SQLException { 
		try {
			return sentencia.executeQuery(query);
		} catch (SQLException ex) {
			throw new SQLException("Excepcio (DAO.java): " + ex.getMessage());
		}
	}
	
	public void register (String user, String pass, String mail) throws SQLException
	{
		try {
			connexio.setAutoCommit(false);
			PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("INSERT INTO usuarios (id_usr, pass, mail) VALUES (?, ?, ?)");
			pstmt.setString(1, user);
			pstmt.setString(2, pass);
			pstmt.setString(3, mail);
			int res = pstmt.executeUpdate();
			if (res == 0) throw new SQLException("Excepcio (DAO.java / register): El usuario no se ha insertado");
			connexio.commit();
			connexio.setAutoCommit(true);
		} catch(SQLException ex){
			throw new SQLException("Excepcio (DAO.java): " + ex.getMessage());
		}
	}
	
	/**public void full_register (String user, String pass, String nom, String cognom, String mail) throws SQLException
	{
		try {
			connexio.setAutoCommit(false);
			//String query = ";
			PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("INSERT INTO usuarios (id_usuario, pass, nom, cognom, mail) " +
						   "VALUES (?,?,?,?,?);");
			pstmt.setString(1, user);
			pstmt.setString(2, pass);
			pstmt.setString(3, nom);
			pstmt.setString(4, cognom);
			pstmt.setString(5, mail);
			pstmt.execute();
			//sentencia.close();
			pstmt.close();
			connexio.commit();
			connexio.setAutoCommit(true);
	
		} catch(SQLException ex){
			throw new SQLException("Excepcio (DAO.java): " + ex.getMessage());
		}
	}*/
	
	// TODO: Afegir codi per fer updates
	/*En les següents pràcitques haureu d'afegir el codi necessari per poder modificar les dades de la BD.*/
	public void desconnectarBD() throws SQLException {
		sentencia.close();
		connexio.close();
	}
	
	public ResultSet recuperaUsuario(String id_usuario) throws SQLException {
		PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("select * from usuarios where id_usr = ? or mail= ?");
		pstmt.setString(1, id_usuario);
		pstmt.setString(2, id_usuario);
		System.out.println(pstmt.toString());
		return pstmt.executeQuery();
	}
	
	public ResultSet loginUsuario(String user, String pass) throws SQLException {
		PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("select id_usr from usuarios where (id_usr= ? and pass = ?) or" +
																					" (mail= ? and pass = ?);");
		pstmt.setString(1, user);
		pstmt.setString(2, pass);
		pstmt.setString(3, user);
		pstmt.setString(4, pass);
		System.out.println("DAO: " + pstmt.toString());
		ResultSet rs = pstmt.executeQuery();
 		return rs;
	} 

	public boolean existeUsuario(String usu) throws SQLException {
		PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("select id_usr from usuarios where id_usr=?;");
		pstmt.setString(1, usu);
		ResultSet rs = pstmt.executeQuery();
 		return rs.next();
	}
	
	public boolean existeMail(String mail) throws SQLException {
		PreparedStatement pstmt = (PreparedStatement) connexio.prepareStatement("select mail from usuarios where mail=?;");
		pstmt.setString(1, mail);
		ResultSet rs = pstmt.executeQuery();
 		return rs.next();
	}
}